package org.easyexcel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.StringUtils;
import org.apache.commons.collections4.MapUtils;
import org.apache.poi.ss.util.CellRangeAddress;
import org.myutil.ExcelToListReader;

import java.io.IOException;
import java.util.*;

/**
 * @ClassName Data2Excel
 * @Description
 * @Author c.w
 * @Date 2025/7/25 10:57
 * @Version 1.0.0
 */
public class Data2Excel {
	public static void main(String[] args) {
		try {
			long startTime = System.currentTimeMillis();
			List<ExpensesRecordExcelVo> list = ExcelToListReader.readFromExcel("D:\\file\\cxtk\\加油卡\\消费记录汇总优化\\测试数据.xlsx", ExpensesRecordExcelVo.class);
			String fileName = "D:\\file\\cxtk\\加油卡\\消费记录汇总优化\\测试输出文件6.xls";
			List<CellRangeAddress> mergeRangeList = new ArrayList<>();
			List<ExpensesRecordExcelVo> res = insertTotalRow(list , mergeRangeList);
			Set<String> includeColumnFiledNames = getOutFiled();

			HashSet<Integer> rightCol = new HashSet<>();
			rightCol.add(7);
			rightCol.add(8);
			rightCol.add(10);
			rightCol.add(11);
			ConsumeRecordWriteHandler styleHandler = new ConsumeRecordWriteHandler(rightCol);
			HashSet<Integer> mergeCol = new HashSet<>();
			mergeCol.add(0);
			EasyExcel.write(fileName, ExpensesRecordExcelVo.class).includeColumnFiledNames(includeColumnFiledNames)
					.sheet("消费记录汇总")
					.relativeHeadRowIndex(2)
					.registerWriteHandler(new ConsumeRecordSheetWriteHandler(mergeRangeList))
					.registerWriteHandler(styleHandler)
					.doWrite(res);

			long endTime = System.currentTimeMillis();
			System.out.println("耗时：" + (endTime - startTime) + "ms");
		} catch (IOException | InstantiationException | IllegalAccessException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}

	public static Map<String, List<ExpensesRecordExcelVo>> groupRecordsByDept(List<ExpensesRecordExcelVo> dataList) {
		LinkedHashMap<String, List<ExpensesRecordExcelVo>> datasMap = new LinkedHashMap<>();//存放主卡单位分组后的数据
		for (int i = 0; i < dataList.size(); i++) {
			ExpensesRecordExcelVo re = dataList.get(i);
			//把键空的先用&代替
			String dtkey = (StringUtils.isEmpty(re.getF_card_dept()) ? "&" : re.getF_card_dept()) + "_";
			if (datasMap.containsKey(dtkey)) {//包含该键，更新值
				List<ExpensesRecordExcelVo> vdlist = datasMap.get(dtkey);
				vdlist.add(re);
			} else {//不包含，添加新值
				List<ExpensesRecordExcelVo> vList = new ArrayList<ExpensesRecordExcelVo>();
				vList.add(re);
				datasMap.put(dtkey, vList);
			}
		}
		return datasMap;
	}

	public static List<ExpensesRecordExcelVo> insertTotalRow(List<ExpensesRecordExcelVo> list , List<CellRangeAddress> mergeRangeList) {
		Map<String, List<ExpensesRecordExcelVo>> deptMap = groupRecordsByDept(list);
		List<ExpensesRecordExcelVo> res = new ArrayList<>();
		if (MapUtils.isNotEmpty(deptMap)) {
			for (Map.Entry<String, List<ExpensesRecordExcelVo>> entry : deptMap.entrySet()) {
				int beforeSize = res.size();
				List<ExpensesRecordExcelVo> dataList = entry.getValue();
				String cardDept = entry.getKey().replace("_", "");
				// 合计圈存
				ExpensesRecordExcelVo total1 = new ExpensesRecordExcelVo();
				total1.setF_card_dept(cardDept);
				total1.setF_use_dept("总计：");
				total1.setF_trading_type("圈存");
				total1.setF_amount(0.0);
				total1.setF_oil_quantity(0.0);
				total1.setF_oil_price(null);
				total1.setF_balance(null);
				// 合计加油
				ExpensesRecordExcelVo total2 = new ExpensesRecordExcelVo();
				total2.setF_card_dept(cardDept);
				total2.setF_use_dept("总计：");
				total2.setF_trading_type("加油");
				total2.setF_amount(0.0);
				total2.setF_oil_quantity(0.0);
				total2.setF_oil_price(null);
				total2.setF_balance(null);
				CellRangeAddress rangeAddress = new CellRangeAddress(res.size()+1, res.size() + 2, 1, 5);
				mergeRangeList.add(rangeAddress);
				res.add(total1);
				res.add(total2);
				// 小计圈存
				ExpensesRecordExcelVo subTotal1 = null;
				//小计加油
				ExpensesRecordExcelVo subTotal2 = null;
				for (int i = 0; i < dataList.size(); i++) {
					if (subTotal1 == null) {
						subTotal1 = new ExpensesRecordExcelVo();
						subTotal1.setF_card_dept(cardDept);
						subTotal1.setF_use_dept("小计：");
						subTotal1.setF_trading_type("圈存");
						subTotal1.setF_amount(0.0);
						subTotal1.setF_oil_quantity(0.0);
						subTotal1.setF_oil_price(null);
						subTotal1.setF_balance(null);
						subTotal2 = new ExpensesRecordExcelVo();
						subTotal2.setF_card_dept(cardDept);
						subTotal2.setF_use_dept("小计：");
						subTotal2.setF_trading_type("加油");
						subTotal2.setF_amount(0.0);
						subTotal2.setF_oil_quantity(0.0);
						subTotal2.setF_oil_price(null);
						subTotal2.setF_balance(null);
					}
					res.add(dataList.get(i));
					if (i + 1 < dataList.size()) {
						if (dataList.get(i).getF_subcard_no().equals(dataList.get(i + 1).getF_subcard_no())) {
							String type = dataList.get(i).getF_trading_type();
							double amount = dataList.get(i).getF_amount();
							double oilQuantity = dataList.get(i).getF_oil_quantity();
							if (type.equals("圈存")) {
								subTotal1.setF_amount(subTotal1.getF_amount() + amount);
								subTotal1.setF_oil_quantity(subTotal1.getF_oil_quantity() + oilQuantity);
							}
							if (type.equals("加油")) {
								subTotal2.setF_amount(subTotal2.getF_amount() + amount);
								subTotal2.setF_oil_quantity(subTotal2.getF_oil_quantity() + oilQuantity);
							}
						} else {
							String type = dataList.get(i).getF_trading_type();
							double amount = dataList.get(i).getF_amount();
							double oilQuantity = dataList.get(i).getF_oil_quantity();
							if (type.equals("圈存")) {
								subTotal1.setF_amount(subTotal1.getF_amount() + amount);
								subTotal1.setF_oil_quantity(subTotal1.getF_oil_quantity() + oilQuantity);
							}
							if (type.equals("加油")) {
								subTotal2.setF_amount(subTotal2.getF_amount() + amount);
								subTotal2.setF_oil_quantity(subTotal2.getF_oil_quantity() + oilQuantity);
							}

							res.add(subTotal1);
							res.add(subTotal2);
							CellRangeAddress range = new CellRangeAddress(res.size() - 1, res.size() , 1, 5);
							mergeRangeList.add(range);
							total1.setF_amount(total1.getF_amount() + subTotal1.getF_amount());
							total1.setF_oil_quantity(total1.getF_oil_quantity() + subTotal1.getF_oil_quantity());
							total2.setF_amount(total2.getF_amount() + subTotal2.getF_amount());
							total2.setF_oil_quantity(total2.getF_oil_quantity() + subTotal2.getF_oil_quantity());

							subTotal1 = new ExpensesRecordExcelVo();
							subTotal1.setF_card_dept(cardDept);
							subTotal1.setF_use_dept("小计：");
							subTotal1.setF_trading_type("圈存");
							subTotal1.setF_amount(0.0);
							subTotal1.setF_oil_quantity(0.0);
							subTotal1.setF_oil_price(null);
							subTotal1.setF_balance(null);
							subTotal2 = new ExpensesRecordExcelVo();
							subTotal2.setF_card_dept(cardDept);
							subTotal2.setF_use_dept("小计：");
							subTotal2.setF_trading_type("加油");
							subTotal2.setF_amount(0.0);
							subTotal2.setF_oil_quantity(0.0);
							subTotal2.setF_oil_price(null);
							subTotal2.setF_balance(null);
						}
					} else {
						String type = dataList.get(i).getF_trading_type();
						double amount = dataList.get(i).getF_amount();
						double oilQuantity = dataList.get(i).getF_oil_quantity();
						if (type.equals("圈存")) {
							subTotal1.setF_amount(subTotal1.getF_amount() + amount);
							subTotal1.setF_oil_quantity(subTotal1.getF_oil_quantity() + oilQuantity);
						}
						if (type.equals("加油")) {
							subTotal2.setF_amount(subTotal2.getF_amount() + amount);
							subTotal2.setF_oil_quantity(subTotal2.getF_oil_quantity() + oilQuantity);
						}
						res.add(subTotal1);
						res.add(subTotal2);
						CellRangeAddress range = new CellRangeAddress(res.size() - 1, res.size() , 1, 5);
						mergeRangeList.add(range);

						total1.setF_amount(total1.getF_amount() + subTotal1.getF_amount());
						total1.setF_oil_quantity(total1.getF_oil_quantity() + subTotal1.getF_oil_quantity());
						total2.setF_amount(total2.getF_amount() + subTotal2.getF_amount());
						total2.setF_oil_quantity(total2.getF_oil_quantity() + subTotal2.getF_oil_quantity());
					}
				}
				int afterSize = res.size();
				CellRangeAddress range = new CellRangeAddress(beforeSize + 1, afterSize , 0, 0);
				mergeRangeList.add(range);
			}
		}
		return res;
	}

	private static Set<String> getOutFiled() {
		Set<String> includeColumnFiledNames = new HashSet<String>();
		includeColumnFiledNames.add("f_card_dept");
		includeColumnFiledNames.add("f_use_dept");
		includeColumnFiledNames.add("f_subcard_type");
		includeColumnFiledNames.add("f_car_no");
		includeColumnFiledNames.add("f_subcard_no");
		includeColumnFiledNames.add("f_subcard_company");
		includeColumnFiledNames.add("f_trading_type");
		includeColumnFiledNames.add("f_amount");
		includeColumnFiledNames.add("f_oil_quantity");
		includeColumnFiledNames.add("f_oil_type");
		includeColumnFiledNames.add("f_oil_price");
		includeColumnFiledNames.add("f_balance");
		includeColumnFiledNames.add("f_business_time");
		includeColumnFiledNames.add("f_trading_time");
		includeColumnFiledNames.add("f_address");
		return includeColumnFiledNames;
	}
}